--    Program: 		CAPPOIMP_RECEIPT_TRANSACTION.sql
--    Description:  Import RCV TRANSACTIONS information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);

   l_transaction_id         number;
                    		                 		
   cursor cur_rcv_transactions is
      select * from CRST_RCV_TRANSACTIONS for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_rcv_transactions loop   	
     l_err_msg_tmp := '';
     
     select RCV_TRANSACTIONS_S.NEXTVAL 
     INTO l_transaction_id
     FROM DUAL;
     
     update CRST_RCV_TRANSACTIONS
     set    transaction_id_n = l_transaction_id
     where  transaction_id = cur.TRANSACTION_ID;
     
     begin
       -- import data
       insert into RCV_TRANSACTIONS
       (
            TRANSACTION_ID                ,
            LAST_UPDATE_DATE              ,
            LAST_UPDATED_BY               ,
            CREATION_DATE                 ,
            CREATED_BY                    ,
            LAST_UPDATE_LOGIN             ,
            REQUEST_ID                    ,
            PROGRAM_APPLICATION_ID        ,
            PROGRAM_ID                    ,
            PROGRAM_UPDATE_DATE           ,
            TRANSACTION_TYPE              ,
            TRANSACTION_DATE              ,
            QUANTITY                      ,
            UNIT_OF_MEASURE               ,
            SHIPMENT_HEADER_ID            ,
            SHIPMENT_LINE_ID              ,
            USER_ENTERED_FLAG             ,
            INTERFACE_SOURCE_CODE         ,
            INTERFACE_SOURCE_LINE_ID      ,
            INV_TRANSACTION_ID            ,
            SOURCE_DOCUMENT_CODE          ,
            DESTINATION_TYPE_CODE         ,
            PRIMARY_QUANTITY              ,
            PRIMARY_UNIT_OF_MEASURE       ,
            UOM_CODE                      ,
            EMPLOYEE_ID                   ,
            PARENT_TRANSACTION_ID         ,
            PO_HEADER_ID                  ,
            PO_RELEASE_ID                 ,
            PO_LINE_ID                    ,
            PO_LINE_LOCATION_ID           ,
            PO_DISTRIBUTION_ID            ,
            PO_REVISION_NUM               ,
            REQUISITION_LINE_ID           ,
            PO_UNIT_PRICE                 ,
            CURRENCY_CODE                 ,
            CURRENCY_CONVERSION_TYPE      ,
            CURRENCY_CONVERSION_RATE      ,
            CURRENCY_CONVERSION_DATE      ,
            ROUTING_HEADER_ID             ,
            ROUTING_STEP_ID               ,
            DELIVER_TO_PERSON_ID          ,
            DELIVER_TO_LOCATION_ID        ,
            VENDOR_ID                     ,
            VENDOR_SITE_ID                ,
            ORGANIZATION_ID               ,
            SUBINVENTORY                  ,
            LOCATOR_ID                    ,
            WIP_ENTITY_ID                 ,
            WIP_LINE_ID                   ,
            WIP_REPETITIVE_SCHEDULE_ID    ,
            WIP_OPERATION_SEQ_NUM         ,
            WIP_RESOURCE_SEQ_NUM          ,
            BOM_RESOURCE_ID               ,
            LOCATION_ID                   ,
            SUBSTITUTE_UNORDERED_CODE     ,
            RECEIPT_EXCEPTION_FLAG        ,
            INSPECTION_STATUS_CODE        ,
            ACCRUAL_STATUS_CODE           ,
            INSPECTION_QUALITY_CODE       ,
            VENDOR_LOT_NUM                ,
            RMA_REFERENCE                 ,
            COMMENTS                      ,
            ATTRIBUTE_CATEGORY            ,
            ATTRIBUTE1                    ,
            ATTRIBUTE2                    ,
            ATTRIBUTE3                    ,
            ATTRIBUTE4                    ,
            ATTRIBUTE5                    ,
            ATTRIBUTE6                    ,
            ATTRIBUTE7                    ,
            ATTRIBUTE8                    ,
            ATTRIBUTE9                    ,
            ATTRIBUTE10                   ,
            ATTRIBUTE11                   ,
            ATTRIBUTE12                   ,
            ATTRIBUTE13                   ,
            ATTRIBUTE14                   ,
            ATTRIBUTE15                   ,
            REQ_DISTRIBUTION_ID           ,
            DEPARTMENT_CODE               ,
            REASON_ID                     ,
            DESTINATION_CONTEXT           ,
            LOCATOR_ATTRIBUTE             ,
            CHILD_INSPECTION_FLAG         ,
            SOURCE_DOC_UNIT_OF_MEASURE    ,
            SOURCE_DOC_QUANTITY           ,
            INTERFACE_TRANSACTION_ID      ,
            GROUP_ID                      ,
            MOVEMENT_ID                   ,
            INVOICE_ID                    ,
            INVOICE_STATUS_CODE           ,
            QA_COLLECTION_ID              ,
            MRC_CURRENCY_CONVERSION_TYPE  ,
            MRC_CURRENCY_CONVERSION_DATE  ,
            MRC_CURRENCY_CONVERSION_RATE  ,
            COUNTRY_OF_ORIGIN_CODE        ,
            MVT_STAT_STATUS               ,
            QUANTITY_BILLED               ,
            MATCH_FLAG                    ,
            AMOUNT_BILLED                 ,
            MATCH_OPTION                  ,
            OE_ORDER_HEADER_ID            ,
            OE_ORDER_LINE_ID              ,
            CUSTOMER_ID                   ,
            CUSTOMER_SITE_ID              ,
            LPN_ID                        ,
            TRANSFER_LPN_ID               ,
            MOBILE_TXN                    ,
            SECONDARY_QUANTITY            ,
            SECONDARY_UNIT_OF_MEASURE     ,
            QC_GRADE                      ,
            SECONDARY_UOM_CODE            ,
            PA_ADDITION_FLAG              ,
            CONSIGNED_FLAG                
       )
       values
       (
            l_transaction_id                  ,
            cur.LAST_UPDATE_DATE              ,
            CUR.LAST_UPDATED_BY,
            cur.CREATION_DATE                 ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.LAST_UPDATE_LOGIN             ,
            cur.REQUEST_ID                    ,
            cur.PROGRAM_APPLICATION_ID        ,
            cur.PROGRAM_ID                    ,
            cur.PROGRAM_UPDATE_DATE           ,
            cur.TRANSACTION_TYPE              ,
            cur.TRANSACTION_DATE              ,
            cur.QUANTITY                      ,
            cur.UNIT_OF_MEASURE               ,
            CAPPOIMP_MAPPING.GetReceiptHeaderIDMapping(cur.SHIPMENT_HEADER_ID),
            CAPPOIMP_MAPPING.GetReceiptLineIDMapping(cur.SHIPMENT_LINE_ID),
            cur.USER_ENTERED_FLAG             ,
            cur.INTERFACE_SOURCE_CODE         ,
            cur.INTERFACE_SOURCE_LINE_ID      ,
            cur.INV_TRANSACTION_ID            ,
            cur.SOURCE_DOCUMENT_CODE          ,
            cur.DESTINATION_TYPE_CODE         , 
            cur.PRIMARY_QUANTITY              ,
            cur.PRIMARY_UNIT_OF_MEASURE       ,
            cur.UOM_CODE                      ,
            CAPBRIMP_MAPPING.GetEmployeeMapping(cur.EMPLOYEE_ID),
            cur.PARENT_TRANSACTION_ID         ,
            CAPPOIMP_MAPPING.GetPOHeaderIDMapping(cur.PO_HEADER_ID),
            cur.PO_RELEASE_ID                 ,
            CAPPOIMP_MAPPING.GetPOLineIDMapping(cur.PO_LINE_ID),
            CAPPOIMP_MAPPING.GetPOLineLocationIDMapping(cur.PO_LINE_LOCATION_ID),
            CAPPOIMP_MAPPING.GetPODistributionIDMapping(cur.PO_DISTRIBUTION_ID),
            cur.PO_REVISION_NUM               ,
            CAPPOIMP_MAPPING.GetPRLineIDMapping(cur.REQUISITION_LINE_ID),
            cur.PO_UNIT_PRICE                 ,
            cur.CURRENCY_CODE                 ,
            cur.CURRENCY_CONVERSION_TYPE      ,
            cur.CURRENCY_CONVERSION_RATE      ,
            cur.CURRENCY_CONVERSION_DATE      ,
            cur.ROUTING_HEADER_ID             ,
            cur.ROUTING_STEP_ID               ,
            CAPBRIMP_MAPPING.GetEmployeeMapping(cur.DELIVER_TO_PERSON_ID),
            CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.DELIVER_TO_LOCATION_ID), 
            CAPPOIMP_MAPPING.GetVendorIDMapping(cur.VENDOR_ID),
            CAPPOIMP_MAPPING.GetVendorSiteIDMapping(cur.VENDOR_SITE_ID),
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.ORGANIZATION_ID),
            cur.SUBINVENTORY                  ,
            cur.LOCATOR_ID                    , --NULL
            cur.WIP_ENTITY_ID                 ,
            cur.WIP_LINE_ID                   ,
            cur.WIP_REPETITIVE_SCHEDULE_ID    ,
            cur.WIP_OPERATION_SEQ_NUM         ,
            cur.WIP_RESOURCE_SEQ_NUM          ,
            cur.BOM_RESOURCE_ID               ,
            CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.LOCATION_ID),
            cur.SUBSTITUTE_UNORDERED_CODE     , 
            cur.RECEIPT_EXCEPTION_FLAG        ,
            cur.INSPECTION_STATUS_CODE        ,
            cur.ACCRUAL_STATUS_CODE           ,
            cur.INSPECTION_QUALITY_CODE       ,
            cur.VENDOR_LOT_NUM                ,
            cur.RMA_REFERENCE                 ,
            cur.COMMENTS                      ,
            cur.ATTRIBUTE_CATEGORY            ,
            cur.ATTRIBUTE1                    ,
            cur.ATTRIBUTE2                    ,
            cur.ATTRIBUTE3                    ,
            cur.ATTRIBUTE4                    ,
            cur.ATTRIBUTE5                    ,
            cur.ATTRIBUTE6                    ,
            cur.ATTRIBUTE7                    ,
            cur.ATTRIBUTE8                    ,
            cur.ATTRIBUTE9                    ,
            cur.ATTRIBUTE10                   ,
            cur.ATTRIBUTE11                   ,
            cur.ATTRIBUTE12                   ,
            cur.ATTRIBUTE13                   ,
            cur.ATTRIBUTE14                   ,
            cur.ATTRIBUTE15                   ,
            CAPPOIMP_MAPPING.GetPRDistributionIDMapping(cur.REQ_DISTRIBUTION_ID),
            cur.DEPARTMENT_CODE               ,
            cur.REASON_ID                     ,
            cur.DESTINATION_CONTEXT           ,
            cur.LOCATOR_ATTRIBUTE             ,
            cur.CHILD_INSPECTION_FLAG         ,
            cur.SOURCE_DOC_UNIT_OF_MEASURE    ,
            cur.SOURCE_DOC_QUANTITY           ,
            cur.INTERFACE_TRANSACTION_ID      ,
            cur.GROUP_ID                      ,
            cur.MOVEMENT_ID                   ,
            cur.INVOICE_ID                    , --NULL
            cur.INVOICE_STATUS_CODE           ,
            cur.QA_COLLECTION_ID              ,
            cur.MRC_CURRENCY_CONVERSION_TYPE  ,
            cur.MRC_CURRENCY_CONVERSION_DATE  ,
            cur.MRC_CURRENCY_CONVERSION_RATE  ,
            cur.COUNTRY_OF_ORIGIN_CODE        ,
            cur.MVT_STAT_STATUS               ,
            cur.QUANTITY_BILLED               ,
            cur.MATCH_FLAG                    ,
            cur.AMOUNT_BILLED                 ,
            cur.MATCH_OPTION                  ,
            cur.OE_ORDER_HEADER_ID            ,
            cur.OE_ORDER_LINE_ID              ,
            cur.CUSTOMER_ID                   , --NULL
            cur.CUSTOMER_SITE_ID              , --NULL
            cur.LPN_ID                        ,
            cur.TRANSFER_LPN_ID               ,
            cur.MOBILE_TXN                    ,
            cur.SECONDARY_QUANTITY            ,
            cur.SECONDARY_UNIT_OF_MEASURE     ,
            cur.QC_GRADE                      ,
            cur.SECONDARY_UOM_CODE            ,
            cur.PA_ADDITION_FLAG              ,
            cur.CONSIGNED_FLAG                
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_RECEIPT_TRANSACTION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
